"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : 外部表安全测试
Case Name   : 越权读取postgresql.conf文件，合理报错
Create At   : 2023/06/07
Owner       : opentestcase033
Description :
    1. 创建用户
    2. 创建外部server
    3. 使用file_fdw创建的外部表读取配置文件
    4. 清理环境
Expect      :
    1. 成功
    2. 成功
    3. 报错，sysadmin用户也无法利用file_fdw读取配置文件
    4. 成功
History     :
"""

import os
import unittest

from yat.test import macro
from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant


class SecurityDesign(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.cons = Constant()
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.user = 'u_privilege_0023'
        self.server = 'ser_privilege_0023'
        self.conf_file = os.path.join(macro.DB_INSTANCE_PATH,
                                      macro.DB_PG_CONFIG_NAME)

    def test_security(self):
        text1 = '-----step1:创建测试用户;expect:成功-----'
        self.log.info(text1)
        msg = self.pri_sh.execut_db_sql(
            f"drop user if exists {self.user} cascade;"
            f"create user {self.user} sysadmin "
            f"password '{macro.COMMON_PASSWD}';")
        self.log.info(msg)
        self.assertIn(self.cons.CREATE_ROLE_SUCCESS_MSG, msg, f'执行失败:{text1}')

        text2 = '-----step2:初始用户创建外部server;expect:成功-----'
        self.log.info(text2)
        msg = self.pri_sh.execut_db_sql(
            f"drop server if exists {self.server};"
            f"create server {self.server} foreign data wrapper file_fdw;"
            f"grant usage on foreign server {self.server} to {self.user};")
        self.log.info(msg)
        self.assertIn(self.cons.create_server_success, msg, f'执行失败：{text2}')
        self.assertIn(self.cons.GRANT_SUCCESS_MSG, msg, f'执行失败：{text2}')

        text3 = '-----step3:使用file_fdw创建的外部表读取配置文件;expect:报错-----'
        self.log.info(text3)
        sql = f'''create foreign table t_privilege_0023(
        id integer, name varchar(20), departno integer, age integer) 
        server {self.server} options (filename '{self.conf_file}', 
        format 'csv', header 'true', delimiter '#');'''
        msg = self.pri_sh.execut_db_sql(
            sql, f'-U {self.user} -W{macro.COMMON_PASSWD}')
        self.log.info(msg)
        self.assertIn('ERROR:  Dist fdw are only available for the supper '
                      'user and Operatoradmin', msg, f'执行失败：{text3}')

    def tearDown(self):
        text4 = '-----step4:清理数据;expect:成功-----'
        self.log.info(text4)
        sql_cmd = f"drop user {self.user} cascade;" \
                  f"drop server {self.server} cascade"
        msg = self.pri_sh.execut_db_sql(sql_cmd)
        self.log.info(msg)
        self.assertIn(self.cons.drop_server_success, msg, f"执行失败:{text4}")
        self.assertIn(self.cons.DROP_ROLE_SUCCESS_MSG, msg, f"执行失败:{text4}")
        self.log.info(f'-----{os.path.basename(__file__)} end-----')

